﻿using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Eval;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;

namespace ExcelMergeTool
{
    public class NPOIUtil
    {
        public const string _defualtSheetName = "sheet";

        #region ReadExcel
        public static DataSet ReadExcel(string file, int startColIndex = 0, int startRowIndex = 0, bool isNeedHeader = false)
        {
            //打开文件
            IWorkbook workbook = GetWorkbook(file);
            var ds = new DataSet();
            //循环遍历Sheet
            for (int sheetIndex = 0; sheetIndex < workbook.NumberOfSheets; sheetIndex++)
            {
                var dt = ReadSheet(workbook, sheetIndex, startColIndex, startRowIndex, isNeedHeader);
                if (dt != null)
                {
                    ds.Tables.Add(dt);
                }
            }
            return ds;
        }

        public static IWorkbook GetWorkbook(string file)
        {
            IWorkbook workbook = null;
            if (string.IsNullOrEmpty(file)) return workbook;
            try
            {
                using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
                {
                    //根据路径通过已存在的excel来创建HSSFWorkbook，即整个excel文档
                    if (file.ToLower().EndsWith(".xlsx")) // 2007版本
                        workbook = new XSSFWorkbook(fs);
                    else if (file.ToLower().EndsWith(".xls")) // 2003版本
                        workbook = new HSSFWorkbook(fs);
                }
            }
            catch
            {
                using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
                {
                    if (file.ToLower().EndsWith(".xlsx")) // 2007版本
                        workbook = new HSSFWorkbook(fs);
                    else if (file.ToLower().EndsWith(".xls")) // 2003版本
                        workbook = new XSSFWorkbook(fs);
                }
            }

            return workbook;
        }

        public static ISheet GetSheet(IWorkbook workbook, int index = 0)
        {
            return workbook.GetSheetAt(index);
        }

        public static IRow GetRow(ISheet sheet, int rownum)
        {
            return sheet.GetRow(rownum);
        }

        public static ICell GetCell(IRow row, int col)
        {
            return row.GetCell(col);
        }

        public static ICell GetCell(ISheet sheet, int rownum, int col)
        {
            IRow row = GetRow(sheet, rownum);
            return row.GetCell(col);
        }

        public static ICellStyle GetCellStyle(ICell cell)
        {
            var style = cell.CellStyle;
            return cell.CellStyle;
        }

        public static CellType GetCellType(ICell cell)
        {
            var type = cell.CellType;
            return type;
        }

        public static DataTable ReadSheet(IWorkbook workbook, int sheetIndex, int startColIndex, int startRowIndex, bool isNeedHeader)
        {
            var sheet = workbook.GetSheetAt(sheetIndex);
            if (sheet == null) return null;

            workbook.SetActiveSheet(sheetIndex);
            var dt = new DataTable();
            //读取表头
            if (isNeedHeader)
            {
                var columns = ReadSheetHeader(sheet, startColIndex, startRowIndex);
                if (columns != null && columns.Length != 0)
                {
                    dt.Columns.AddRange(columns);
                }
                startRowIndex++;
            }
            //遍历读取每一行数据
            for (int rowIndex = startRowIndex; rowIndex <= sheet.LastRowNum; rowIndex++)
            {
                var dataRow = dt.NewRow();
                ReadRow(sheet, dataRow, rowIndex, startColIndex);
                if (dataRow != null)
                {
                    dt.Rows.Add(dataRow);
                }
            }
            if (dt.Rows.Count == 0) return null;
            return dt;
        }

        private static DataColumn[] ReadSheetHeader(ISheet sheet, int startColIndex, int headerRowIndex)
        {
            var row = sheet.GetRow(headerRowIndex);
            if (row == null) return null;
            var columns = new DataColumn[row.LastCellNum];

            for (int i = startColIndex; i < row.LastCellNum; ++i)
            {
                columns[i] = new DataColumn(row.GetCell(i).StringCellValue);
            }

            return columns;
        }

        public static void ReadRow(ISheet sheet, DataRow dr, int rowIndex, int startColIndex)
        {
            var row = sheet.GetRow(rowIndex);
            if (row == null)
            {
                dr = null;
                return;
            }
            //遍历读取每个单元格
            for (int colIndex = startColIndex; colIndex < dr.ItemArray.Length; ++colIndex)
            {
                dr[colIndex] = ReadCell(row, colIndex);
            }
        }

        public static object ReadCell(IRow row, int colIndex)
        {
            object obj = null;
            var cell = row.GetCell(colIndex);
            if (cell == null) return obj;
            switch (cell.CellType)
            {
                case CellType.String:
                    obj = cell.StringCellValue;
                    break;
                case CellType.Boolean:
                    obj = cell.BooleanCellValue;
                    break;
                case CellType.Error:
                    obj = ErrorEval.GetText(cell.ErrorCellValue);
                    break;
                case CellType.Numeric:
                    if (DateUtil.IsCellDateFormatted(cell))
                    {
                        obj = DateTime.FromOADate(cell.NumericCellValue);
                    }
                    else
                    {
                        obj = cell.NumericCellValue;
                    }
                    break;
                case CellType.Formula:
                    switch (cell.CachedFormulaResultType)
                    {
                        case CellType.Boolean:
                            obj = cell.BooleanCellValue;
                            break;
                        case CellType.Error:
                            obj = ErrorEval.GetText(cell.ErrorCellValue);
                            break;
                        case CellType.Numeric:
                            if (DateUtil.IsCellDateFormatted(cell))
                            {
                                obj = DateTime.FromOADate(cell.NumericCellValue);
                            }
                            else
                            {
                                obj = cell.NumericCellValue;
                            }
                            break;
                        case CellType.String:
                            obj = cell.StringCellValue;
                            break;
                        default:
                            obj = string.Empty;
                            break;
                    }
                    break;

                default:
                    obj = string.Empty;
                    break;
            }

            return obj;
        }
        #endregion

        #region WriteExcel
        public static void WriteExcel(DataSet ds, string file, int startColIndex = 0, int startRowIndex = 0, bool isNeedHeader = false)
        {
            var workbook = WriteWorkbook(ds, file, startColIndex, startRowIndex, isNeedHeader);
            WriteFile(workbook, file);
        }

        public static void WriteExcel(DataTable dt, string file, int startColIndex = 0, int startRowIndex = 0, bool isNeedHeader = false)
        {
            var ds = new DataSet();
            ds.Tables.Add(dt);
            WriteExcel(ds, file, startColIndex, startRowIndex, isNeedHeader);
        }

        public static IWorkbook WriteWorkbook(DataSet ds, string file, int startColIndex = 0, int startRowIndex = 0, bool isNeedHeader = false)
        {
            var workbook = CreateWorkbook(file);
            int sheetIndex = 0;
            foreach (DataTable dt in ds.Tables)
            {
                var sheet = CreateSheet(dt, workbook, ++sheetIndex);
                WriteSheet(dt, sheet, startColIndex, startRowIndex, isNeedHeader);
            }
            return workbook;
        }

        public static ISheet WriteSheet(DataTable dt, ISheet sheet, int startColIndex, int startRowIndex, bool isNeedHeader)
        {
            //处理头列
            if (isNeedHeader)
            {
                WriteSheetHeader(sheet, dt.Columns, startColIndex, startRowIndex);
                startRowIndex++;
            }
            //处理行
            foreach (DataRow dr in dt.Rows)
            {
                WriteRow(sheet, dr, startRowIndex++, startColIndex);
            }

            return sheet;
        }

        public static void WriteSheetHeader(ISheet sheet, DataColumnCollection columns, int startColIndex, int headerRowIndex)
        {
            if (columns == null || columns.Count == 0) return;

            var row = sheet.CreateRow(headerRowIndex);
            foreach (DataColumn column in columns)
            {
                WriteCell(row, startColIndex + column.Ordinal, column.ColumnName);
            }
        }

        public static void WriteRow(ISheet sheet, DataRow dr, int rowIndex, int startColIndex)
        {
            if (dr == null || dr.ItemArray == null || dr.ItemArray.Length == 0) return;
            var row = sheet.CreateRow(rowIndex);
            foreach (DataColumn column in dr.Table.Columns)
            {
                WriteCell(row, startColIndex + column.Ordinal, dr[column].ToString());
            }
        }

        public static IRow CreateRow(ISheet sheet, int rowIndex)
        {
            return sheet.CreateRow(rowIndex);
        }

        public static void WriteCell(IRow row, int colIndex, string vaule)
        {
            row.CreateCell(colIndex).SetCellValue(vaule);
        }

        public static void SetCellValue(ICell cell, string value, ICellStyle style)
        {
            cell.SetCellValue(value);
            if (style != null)
            {
                cell.CellStyle = style;
            }
        }

        public static void SetCellValue(ICell cell, int value, ICellStyle style)
        {
            cell.SetCellValue(value);

            if (style != null)
            {
                cell.CellStyle = style;
            }
        }

        public static void WriteFile(IWorkbook workbook, string file)
        {
            if (File.Exists(file))
            {
                File.Delete(file);
            }
            using (var fs = File.Create(file))
            {
                workbook.Write(fs);
            }
        }

        public static IWorkbook CreateWorkbook(string file)
        {
            IWorkbook workbook = null;
            if (file.ToLower().EndsWith(".xls"))
            {
                workbook = new HSSFWorkbook();
            }
            else
            {
                workbook = new XSSFWorkbook();
            }
            return workbook;
        }

        public static ISheet CreateSheet(DataTable dt, IWorkbook workbook, int sheetIndex)
        {
            var sheetName = (dt.TableName ?? string.Empty) == string.Empty ? string.Format("{0}{1}", _defualtSheetName, sheetIndex.ToString()) : dt.TableName;
            return workbook.CreateSheet(sheetName);
        }

        public static ISheet CreateSheet(IWorkbook workbook, string sheetName)
        {
            return workbook.CreateSheet(sheetName);
        }
        #endregion
    }
}
